#include "database.h"

Database::Database(QObject *parent) : QObject(parent) {
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("pims.db");
}

Database::~Database() {
    if (db.isOpen()) {
        db.close();
    }
}

bool Database::initialize() {
    if (!db.open()) {
        return false;
    }
    return createTables();
}

bool Database::createTables() {
    QSqlQuery query;

    // Create Product table
    if (!query.exec("CREATE TABLE IF NOT EXISTS Product ("
                    "product_id TEXT PRIMARY KEY,"
                    "name TEXT NOT NULL,"
                    "created_at TEXT NOT NULL,"
                    "updated_at TEXT NOT NULL,"
                    "description TEXT"
                    ")")) {
        return false;
    }

    // Create System table
    if (!query.exec("CREATE TABLE IF NOT EXISTS System ("
                    "system_id TEXT PRIMARY KEY,"
                    "name TEXT NOT NULL,"
                    "created_at TEXT NOT NULL,"
                    "updated_at TEXT NOT NULL,"
                    "description TEXT"
                    ")")) {
        return false;
    }

    // Create Component table
    if (!query.exec("CREATE TABLE IF NOT EXISTS Component ("
                    "component_id TEXT PRIMARY KEY,"
                    "name TEXT NOT NULL,"
                    "created_at TEXT NOT NULL,"
                    "updated_at TEXT NOT NULL,"
                    "description TEXT"
                    ")")) {
        return false;
    }

    // Create Part table
    if (!query.exec("CREATE TABLE IF NOT EXISTS Part ("
                    "part_id TEXT PRIMARY KEY,"
                    "name TEXT NOT NULL,"
                    "created_at TEXT NOT NULL,"
                    "updated_at TEXT NOT NULL,"
                    "description TEXT,"
                    "weight REAL,"
                    "supplier TEXT,"
                    "status TEXT"
                    ")")) {
        return false;
    }

    return true;
}

bool Database::addProduct(const QString &product_id, const QString &name, const QString &description) {
    QSqlQuery query;
    query.prepare("INSERT INTO Product (product_id, name, created_at, updated_at, description) "
                  "VALUES (:product_id, :name, datetime('now'), datetime('now'), :description)");
    query.bindValue(":product_id", product_id);
    query.bindValue(":name", name);
    query.bindValue(":description", description);
    if (!query.exec()) {
        qDebug() << "SQL Error:" << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::addSystem(const QString &system_id, const QString &name, const QString &description) {
    QSqlQuery query;
    query.prepare("INSERT INTO System (system_id, name, created_at, updated_at, description) "
                  "VALUES (:system_id, :name, datetime('now'), datetime('now'), :description)");
    query.bindValue(":system_id", system_id);
    query.bindValue(":name", name);
    query.bindValue(":description", description);
    if (!query.exec()) {
        qDebug() << "SQL Error:" << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::addComponent(const QString &component_id, const QString &name, const QString &description) {
    QSqlQuery query;
    query.prepare("INSERT INTO Component (component_id, name, created_at, updated_at, description) "
                  "VALUES (:component_id, :name, datetime('now'), datetime('now'), :description)");
    query.bindValue(":component_id", component_id);
    query.bindValue(":name", name);
    query.bindValue(":description", description);
    if (!query.exec()) {
        qDebug() << "SQL Error:" << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::addPart(const QString &part_id, const QString &name, const QString &description) {
    QSqlQuery query;
    query.prepare("INSERT INTO Part (part_id, name, created_at, updated_at, description) "
                  "VALUES (:part_id, :name, datetime('now'), datetime('now'), :description)");
    query.bindValue(":part_id", part_id);
    query.bindValue(":name", name);
    query.bindValue(":description", description);
    if (!query.exec()) {
        qDebug() << "SQL Error:" << query.lastError().text();
        return false;
    }
    return true;
}

QList<Product> Database::getProducts() {
    QList<Product> products;
    QSqlQuery query("SELECT * FROM Product");
    while (query.next()) {
        Product product;

        product.product_id = query.value("product_id").toString();
        product.name = query.value("name").toString();
        product.description = query.value("description").toString();

        // 处理时间字段（TEXT 存储的是 ISO8601 格式）
        product.created_at = QDateTime::fromString(query.value("created_at").toString(), Qt::ISODate);
        product.updated_at = QDateTime::fromString(query.value("updated_at").toString(), Qt::ISODate);

        // 如果时间格式不对，可以加判断
        if (!product.created_at.isValid()) {
            product.created_at = QDateTime();
        }
        if (!product.updated_at.isValid()) {
            product.updated_at = QDateTime();
        }

        products.append(product);
    }

    return products;
}

QList<System> Database::getSystems() {
    QList<System> systems;
    QSqlQuery query("SELECT * FROM System");
    while (query.next()) {
        System system;

        system.system_id = query.value("system_id").toString();
        system.name = query.value("name").toString();
        system.description = query.value("description").toString();

        // 处理时间字段（TEXT 存储的是 ISO8601 格式）
        system.created_at = QDateTime::fromString(query.value("created_at").toString(), Qt::ISODate);
        system.updated_at = QDateTime::fromString(query.value("updated_at").toString(), Qt::ISODate);

        // 如果时间格式不对，可以加判断
        if (!system.created_at.isValid()) {
            system.created_at = QDateTime();
        }
        if (!system.updated_at.isValid()) {
            system.updated_at = QDateTime();
        }

        systems.append(system);
    }

    return systems;
}

QList<Component> Database::getComponents() {
    QList<Component> components;
    QSqlQuery query("SELECT * FROM Component");
    while (query.next()) {
        Component component;

        component.component_id = query.value("component_id").toString();
        component.name = query.value("name").toString();
        component.description = query.value("description").toString();

        // 处理时间字段（TEXT 存储的是 ISO8601 格式）
        component.created_at = QDateTime::fromString(query.value("created_at").toString(), Qt::ISODate);
        component.updated_at = QDateTime::fromString(query.value("updated_at").toString(), Qt::ISODate);

        // 如果时间格式不对，可以加判断
        if (!component.created_at.isValid()) {
            component.created_at = QDateTime();
        }
        if (!component.updated_at.isValid()) {
            component.updated_at = QDateTime();
        }

        components.append(component);
    }

    return components;
}

QList<Part> Database::getParts() {
    QList<Part> parts;
    QSqlQuery query("SELECT * FROM Part");
    while (query.next()) {
        Part part;

        part.part_id = query.value("part_id").toString();
        part.name = query.value("name").toString();
        part.description = query.value("description").toString();
        part.weight = query.value("weight").toDouble();           // ← 自动转 double
        part.supplier = query.value("supplier").toString();
        part.status = query.value("status").toString();

        // 处理时间字段（TEXT 存储的是 ISO8601 格式）
        part.created_at = QDateTime::fromString(query.value("created_at").toString(), Qt::ISODate);
        part.updated_at = QDateTime::fromString(query.value("updated_at").toString(), Qt::ISODate);

        // 如果时间格式不对，可以加判断
        if (!part.created_at.isValid()) {
            part.created_at = QDateTime();
        }
        if (!part.updated_at.isValid()) {
            part.updated_at = QDateTime();
        }

        parts.append(part);
    }

    return parts;
}

bool Database::updateProduct(const QString &product_id, const QString &name, const QString &description) {
    QSqlQuery query;
    query.prepare("UPDATE Product SET name = :name, updated_at = datetime('now'), description = :description WHERE product_id = :product_id");
    query.bindValue(":product_id", product_id);
    query.bindValue(":name", name);
    query.bindValue(":description", description);
    if (!query.exec()) {
        qDebug() << "SQL Error:" << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::updateSystem(const QString &system_id, const QString &name, const QString &description) {
    QSqlQuery query;
    query.prepare("UPDATE System SET name = :name, updated_at = datetime('now'), description = :description WHERE system_id = :system_id");
    query.bindValue(":system_id", system_id);
    query.bindValue(":name", name);
    query.bindValue(":description", description);
    if (!query.exec()) {
        qDebug() << "SQL Error:" << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::updateComponent(const QString &component_id, const QString &name, const QString &description) {
    QSqlQuery query;
    query.prepare("UPDATE Component SET name = :name, updated_at = datetime('now'), description = :description WHERE component_id = :component_id");
    query.bindValue(":component_id", component_id);
    query.bindValue(":name", name);
    query.bindValue(":description", description);
    if (!query.exec()) {
        qDebug() << "SQL Error:" << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::updatePart(const QString &part_id, const QString &name, const QString &description) {
    QSqlQuery query;
    query.prepare("UPDATE Part SET name = :name, updated_at = datetime('now'), description = :description WHERE part_id = :part_id");
    query.bindValue(":part_id", part_id);
    query.bindValue(":name", name);
    query.bindValue(":description", description);
    //query.bindValue(":weight", weight);
    //query.bindValue(":supplier", supplier);
    //query.bindValue(":status", status);
    if (!query.exec()) {
        qDebug() << "SQL Error:" << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::deleteProduct(const QString &product_id) {
    QSqlQuery query;
    query.prepare("DELETE FROM Product WHERE product_id = :product_id");
    query.bindValue(":product_id", product_id);
    if (!query.exec()) {
        qDebug() << "SQL Error:" << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::deleteSystem(const QString &system_id) {
    QSqlQuery query;
    query.prepare("DELETE FROM System WHERE system_id = :system_id");
    query.bindValue(":system_id", system_id);
    if (!query.exec()) {
        qDebug() << "SQL Error:" << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::deleteComponent(const QString &component_id) {
    QSqlQuery query;
    query.prepare("DELETE FROM Component WHERE component_id = :component_id");
    query.bindValue(":component_id", component_id);
    if (!query.exec()) {
        qDebug() << "SQL Error:" << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::deletePart(const QString &part_id) {
    QSqlQuery query;
    query.prepare("DELETE FROM Part WHERE part_id = :part_id");
    query.bindValue(":part_id", part_id);
    if (!query.exec()) {
        qDebug() << "SQL Error:" << query.lastError().text();
        return false;
    }
    return true;
}